﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Burn4You;
using MediaPortal.Configuration;
using SQLite.NET;

namespace MovingPicturesFilesExt
{
    public class MovingPicturesFilesExt : Extension, IExtension
    {
        #region IExtention members
        
        public String ExtName
        {
            get { return "Moving Pictures File Grabber"; }
        }

        public String Image
        {
            get { return ""; }
        }

        public bool HasFiles
        {
            get { return true; }
        }

        public bool HasUI
        {
            get { return false; }
        }

        public List<String> SupportedBurnTypes()
        {
            List<String> sbt = new List<string>(){"Movie"};
            
            return sbt;
        }

        #endregion

        #region Extension overrides
        
        public override List<BurnFile> GetFiles()
        {
            Utils.Burn4YouLog.Write("Movie Pictures Grabber: Getting files");
            List<BurnFile> Files = new List<BurnFile>();

            SQLite.NET.SQLiteClient m_db = new SQLiteClient(Config.GetFolder(Config.Dir.Database) + @"\movingpictures.db3");

            // This SQL query uses 3 tables to get the fields it wants. One table is the "local_media" table which holds the info on where the files are,
            // the second table is "movie_info" which holds all the info from the online db's like the real name, description, actors, etc. The
            // last table is a joining table which simply lists what record in "local_media" relates to it's assoicated record in "movie_info".
            //
            // The way it does this is by running two sql queries at once. The first statement is in brackets. It uses the "INNER JOIN" command
            // to select data from two tables at once, using the ON and = sign to tell it which fields in both those tables it can use to work out
            // which records are for the same item. In this case the "ID" field in the "movie_info" table is the same as the "movie_info_id" field in
            // the "local_media__movie_info" table. For each ID match in those fields, SQL then returns all the fields for that item from both tables. We
            // use the "SELECT" command to tell it which fields we actually want. In this case we want to return all the fields we need from the
            // "movie_info" table and also the "local_media_id" from the "loca_media__movie_info" table.
            //
            // Now that we have all the fields we want from the "movie_info" table and the "ID" of the movie in the "local_media" table, we can run the 2nd query
            // qith another "INNER JOIN" command to select all the fields we want from the "local_media" table and all the fields from the last SQL command. In this 2nd
            // query we give the "FROM" command the 1st SQL query in brackets. To this 2nd query, the 1st query looks like another table, so we just use the
            // "SELECT" command to select the fields we want to get from the result of the 1st query. We use the "local_media_id" fields from the first query
            // and the "ID" field from the "local_media" table to choose which records to join. This will then return all the records from the first query (which
            // has all the info we want from the "movie_info" table in it) and all the records of the "local_media" table as one giant table. We then use the 
            // "SELECT" command again to select the fields we want from the giant table.
            // 
            // NOTE: If you have two fields with the same name but are unreleated to each other, you can use the "AS" command to change the name of one of the fields
            // so you can use the SELECT command to select both of them. E.G say you have a table with the field "ID" and another table with the field "ID", but they
            // refer to two differnt ID types, then go "SELECT table1.ID AS OnlineID, table1.name". The resulting table will call the ID field "OnlineID" rather then
            // "ID". This will allow you to then use SELECT on the second query to select both fields. EG "Select ID, OnlineID, name".
            SQLiteResultSet result = m_db.Execute(
                "SELECT title, fullpath " +
                "FROM (SELECT movie_info.title, local_media__movie_info.[local_media_id] " +
                "FROM movie_info " +
                "INNER JOIN local_media__movie_info ON movie_info.id=local_media__movie_info.movie_info_id) " +
                "INNER JOIN local_media ON local_media_id=local_media.id");

            foreach (SQLiteResultSet.Row row in result.Rows)
            {
                int fieldno = 0;
                BurnFile file = new BurnFile();

                foreach (String field in row.fields)
                {
                    switch(fieldno)
                    {
                        case 0:
                            file.Name = field;
                            break;
                        case 1:
                            file.FilePath = field;
                            break;
                    }

                    fieldno++;
                }
                file.BurnType = "Movie";
                Files.Add(file);
            }

            m_db.Close();

            Utils.Burn4YouLog.Write("Movie Pictures Grabber: Finished getting files. Files retrieved: " + Files.Count.ToString());
            return Files;
        }

        #endregion
    }
}
